using System;
using System.Data.SQLite;

namespace DataLite
{
    public class DataHelper : IDisposable
    {
        private SQLiteConnection objConnection;
        private SQLiteCommand objCommand;

        /// <summary>
        /// Constructor sin conexion
        /// </summary>
        public DataHelper() 
        {
        }

        /// <summary>
        /// Inicializa la conexion, con la cadena de conexion, pero no la abre
        /// </summary>
        /// <param name="connectionString">Cadena de conexion</param>
        public DataHelper(string connectionString)
        {
            objConnection = new SQLiteConnection();
            objCommand = new SQLiteCommand();

            objConnection.ConnectionString = connectionString;
            objCommand.Connection = objConnection;
        }
        
        #region Execute sql

        public object ExecuteScalar(string sql, SQLiteParameter[] parameters)
        {
            SQLiteCommand command = new SQLiteCommand();
            command.CommandText = sql;
            command.Parameters.AddRange(parameters);
            command.ExecuteScalar();
            return null;
        }

        public SQLiteDataReader ExecuteReader(string sql, SQLiteParameter[] parameters)
        {            
            objCommand.CommandText = sql;
            if (parameters != null) { objCommand.Parameters.AddRange(parameters); }

            SQLiteDataReader reader = null;
            try
            {
                if (objConnection.State == System.Data.ConnectionState.Closed) { objConnection.Open(); }
                reader = objCommand.ExecuteReader();
            }
            catch (Exception ex) { throw ex; }
            finally
            {
                objCommand.Parameters.Clear();
            }
            return reader;
        }

        public void ExecuteNonQuery(string sql, SQLiteParameter[] parameters)
        {
            objCommand.CommandText = sql;

            int i = -1;
            try
            {
                if (objConnection.State == System.Data.ConnectionState.Closed) { objConnection.Open(); }
                i = objCommand.ExecuteNonQuery();
                //executionSucceeded = true;
            }
            catch (Exception ex)
            {
                throw ex;
                //executionSucceeded = false;
                //HandleExceptions(ex);
            }
            finally
            {
                objCommand.Parameters.Clear();
                /*if (connectionstate == ConnectionState.CloseOnExit){objConnection.Close();}*/
            }

            //return i;
        }

        #endregion

        #region Get value to datareader

        public object GetObject(SQLiteDataReader dataReader, string columnName, object valueIfNull)
        {
            object value = dataReader[columnName];            
            if (value != null && value != DBNull.Value) { return value; }
            else { return valueIfNull; }
        }

        public int GetInt32(SQLiteDataReader dataReader, string columnName, int valueIfNull)
        {
            object value = GetObject(dataReader, columnName, null);
            if (value != null)
            {
                if (value is int) { return (int)value; }
                else { return Int32.Parse(value.ToString()); }
            }
            else { return valueIfNull; }
        }

        public string GetString(SQLiteDataReader dataReader, string columnName, string valueIfNull)
        {
            object value = GetObject(dataReader, columnName, null);
            if (value != null)
            {
                if (value is string) { return (string)value; }
                else { return value.ToString(); }
            }
            else { return valueIfNull; }
        }

        #endregion

       
        #region Transaction service
         /*
        public void BeginTransaction()
        {
            if (objConnection.State == System.Data.ConnectionState.Closed)
            {
                objConnection.Open();
            }
            objCommand.Transaction = objConnection.BeginTransaction();
        }


        public void CommitTransaction()
        {
            objCommand.Transaction.Commit();
            objConnection.Close();
        }

        public void RollbackTransaction()
        {
            objCommand.Transaction.Rollback();
            objConnection.Close();
        }
        */
        #endregion
        

        #region IDisposable Members

        public void Dispose()
        {
            objConnection.Close();
            objConnection.Dispose();
            objCommand.Dispose();
        }

        #endregion

    }
}
